package net.phoenix.repository.dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import net.phoenix.repository.SQLParameter;
import net.phoenix.repository.data.NodeData;

/**
 * 
 * @author lixf
 * 
 */
public abstract class NodeTableQueryDao extends JdbcDao {
	/**
	 * 根据ID来查找节点
	 * 
	 * @param ID
	 *            String
	 * @return NodeData
	 * @throws SQLException
	 */
	private static final String SQL_FIND = "select * from NODE where PKID=?";

	public NodeData findByID(String ID) throws SQLException {
		PreparedStatement command = null;
		try {
			command = connection.prepareStatement(SQL_FIND);
			command.setString(1, ID);
			NodeData[] result = this.retrieve(command, SQL_FIND + ",ID=" + ID);
			if (result.length > 0) {
				return result[0];
			}
			return null;
		} finally {
			this.close(command);
		}
	}
	


	/**
	 * retrieve data from database;
	 * @param statement
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	protected NodeData[] retrieve(PreparedStatement statement, String sql) throws SQLException {
		ResultSet rs = statement.executeQuery();
		ArrayList<NodeData> result = new ArrayList<NodeData>();
		while (rs.next()) {
			NodeData group = new NodeData();
			this.populate(rs, group);
			result.add(group);
		}
		rs.close();
		statement.close();
		return (NodeData[]) result.toArray(new NodeData[result.size()]);
	}

	/**
	 * populate data to NodeData object;
	 * @param rs
	 * @param data
	 * @throws SQLException
	 */
	protected void populate(ResultSet rs, NodeData data) throws SQLException {
		data.setID(rs.getString("PKID"));
		data.setParentID(rs.getString("PARENT_ID"));
		data.setName(rs.getString("NAME"));
		data.setOrderNo(rs.getInt("VINDEX"));
		data.setPrimaryType(rs.getString("primaryType"));
		for(int i=0;i<8;i++) {
			String column = "mixinTypes[" + i+ "]";
			String mixin = rs.getString(column);
			if(mixin!=null && mixin.length()>0)
				data.getMixinTypes().add(mixin);
		}
		data.setNew(false);
	}


	/**
	 * 获取指定父结点下的所有子节点，按照分页的模式
	 * 
	 * @param parentID
	 *            String
	 * @param fromIndex
	 *            int
	 * @param number
	 *            int
	 * @return NodeData[]
	 * @throws SQLException
	 */

	public abstract NodeData[] findByParent(String parentID, long fromIndex, long number) throws SQLException;

	public abstract NodeData[] findByParentRecursively(String parentID, String name, long fromIndex, long number) throws SQLException;


	private static final String SQL_Find_By_ParentAndName = "select * from NODE where PARENT_ID=? and NAME = ? order by VINDEX";

	/**
	 * 获取制定父节点下的子节点
	 * 
	 * @param parentID
	 *            String
	 * @param name
	 *            String
	 * @param fromIndex
	 *            long
	 * @param number
	 *            long
	 * @return NodeData[]
	 * @throws SQLException
	 */
	public NodeData[] findByParent(String parentID, String name, long fromIndex, long number) throws SQLException {
		PreparedStatement command = null;
		String sql = toOracleRangeQuery(SQL_Find_By_ParentAndName);
		try {
			command = connection.prepareStatement(sql);
			command.setString(1, parentID);
			command.setString(2, name);
			command.setLong(3, fromIndex + number);
			command.setLong(4, fromIndex);
			NodeData[] result = this.retrieve(command, sql);
			return result;
		} finally {
			this.close(command);
		}
	}

	

	private static final String SQL_MAX_ORDERBYNO = "select Max(VINDEX) from node Where parent_id =? and name=? ";

	/**
	 * 最大序号
	 * 
	 * @param parentID
	 *            String
	 * @param name
	 *            String
	 * @return int
	 * @throws SQLException
	 */
	public int getMaxOrderNo(String parentID, String name) throws SQLException {
		PreparedStatement command = null;
		try {
			command = connection.prepareStatement(SQL_MAX_ORDERBYNO);
			command.setString(1, parentID);
			command.setString(2, name);
			int result = 0;

			ResultSet rs = command.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
			rs.close();
			command.close();
			return result;
		} finally {
			this.close(command);
		}
	}

	/**
	 * 复制内容，调用存储过程实现
	 * 
	 * @param contentID
	 *            String
	 * @throws SQLException
	 */
	public NodeData copy(String NodeID) throws SQLException {
		String ID = "" + getNextID();
		CallableStatement proc = null;
		try {
			/**
			 * liulei modified pro_copy_node2
			 */
			proc = connection.prepareCall("{ call pro_copy_node2(?,?) }");
			proc.setString(1, ID);
			proc.setString(2, NodeID);
			proc.execute();
			return this.findByID(ID);
		} finally {
			proc.close();
		}
	}
	public abstract NodeData[] search(String query, SQLParameter[] parameters, long fromIndex, long number) throws SQLException;

	protected PreparedStatement createStatement(String sql, SQLParameter[] parameters) throws SQLException {
		PreparedStatement command = null;
		command = connection.prepareStatement(sql);
		if ((parameters != null) && (parameters.length > 0)) {
			for (int i = 0; i < parameters.length; i++) {
				Object value = parameters[i].getValue();
				if (value instanceof Date) {
					value = toTimestame((Date) value);
					command.setTimestamp(i + 1, (Timestamp) value);
				} else {
					command.setObject(i + 1, value);
				}
			}
		}
		return command;
	}
}
